package com.rybbaby.tss.tasks.core.web.dao.imp;

import com.rybbaby.tss.tasks.core.bean.PageBean;
import com.rybbaby.tss.tasks.core.utils.DBTypeUtil;
import com.rybbaby.tss.tasks.core.web.dao.IGenericDao;
import org.apache.commons.lang.StringEscapeUtils;
import org.apache.commons.lang.StringUtils;
import org.apache.log4j.Logger;
import org.hibernate.*;
import org.hibernate.criterion.*;
import org.hibernate.transform.ResultTransformer;
import org.hibernate.transform.Transformers;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.dao.EmptyResultDataAccessException;
import org.springframework.dao.support.DataAccessUtils;
import org.springframework.jdbc.core.CallableStatementCreator;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.SqlParameter;
import org.springframework.jdbc.core.namedparam.MapSqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.orm.hibernate5.HibernateTemplate;
import org.springframework.transaction.annotation.Transactional;
import org.springframework.util.Assert;

import javax.persistence.Column;
import javax.persistence.Id;
import javax.persistence.Table;
import java.io.Serializable;
import java.lang.InstantiationException;
import java.lang.reflect.Field;
import java.lang.reflect.Method;
import java.math.BigDecimal;
import java.sql.*;
import java.text.MessageFormat;
import java.util.*;

/**
 * @author 谢进伟
 * @description DAO层泛型基类
 * @createDate 2015-11-10 下午4:24:40
 */
public abstract class GenericDao<T> implements IGenericDao<T> {

	private static final Logger logger = Logger.getLogger(GenericDao.class);

	public GenericDao (){
		super();
	}

	@Autowired
	protected SessionFactory sessionFactory;

	@Autowired
	protected JdbcTemplate jdbcTemplate;

	@Autowired
	protected HibernateTemplate hibernateTemplate;

	@Autowired
	protected NamedParameterJdbcTemplate namedParameterJdbcTemplate;
	/**
	 * 有结果集存储过程结果集数据
	 **/
	private List<Map<String , Object>> callableStatementResultList = null;

	/**
	 * 数据库类型
	 */
	public static final String DATABSE_TYPE_MYSQL = "mysql";
	public static final String DATABSE_TYPE_POSTGRE = "postgresql";
	public static final String DATABSE_TYPE_ORACLE = "oracle";
	public static final String DATABSE_TYPE_SQLSERVER = "sqlserver";

	/**
	 * 分页SQL
	 */
	public static final String MYSQL_SQL = "select * from ( {0}) sel_tab00 limit {1},{2}";
	public static final String POSTGRE_SQL = "select * from ( {0}) sel_tab00 limit {2} offset {1}";
	public static final String ORACLE_SQL = "select * from (select row_.*,rownum rownum_ from ({0}) row_ where rownum <= {1}) where rownum_>{2}";
	public static final String SQLSERVER_SQL = "select * from ( select row_number() over(order by tempColumn) tempRowNumber, * from (select top {1} tempColumn = 0, {0}) t ) tt where tempRowNumber > {2}";

	public Session getSession() {
		// 事务必须是开启的(Required)，否则获取不到
		return this.sessionFactory.getCurrentSession();
	}

	@Override
	public List<T> loadByMultipleId(Class<T> cls , Object [] Ids) {
		return this.getSession().byMultipleIds(cls).multiLoad(Ids);
	}

	@Override
	public List<T> loadByMultipleId(Class<T> cls , List<String> ids) {
		return this.getSession().byMultipleIds(cls).multiLoad(ids);
	}

	/**
	 * 根据传入的实体持久化对象
	 *
	 * @param entity
	 *            需要持久化的对象
	 */

	@Override
	public Serializable save(T entity) {
		try {
			Serializable id = hibernateTemplate.save(entity);
			hibernateTemplate.flush();
			if(logger.isDebugEnabled()) {
				logger.debug("保存实体成功," + entity.getClass().getName());
			}
			return id;
		} catch (RuntimeException e) {
			logger.error("保存实体异常" , e);
			throw e;
		}
	}

	/**
	 * 批量保存实体
	 *
	 * @param entitys
	 *            需要保存的实体集合
	 */

	@Override
	public boolean jdbcBatchSave(List<T> entitys) throws Exception {
		boolean result = false;
		if(entitys.size() > 0) {
			T t = entitys.get(0);
			String sql = this.getBatchInsertSql(t);
			List<Object []> batchArgs = this.getBatchInsertArgs(entitys);
			int [] batchSaveResult = this.jdbcTemplate.batchUpdate(sql , batchArgs);
			if(batchSaveResult != null && batchSaveResult.length > 0) {
				Arrays.sort(batchSaveResult);
				result = batchSaveResult[0] > 0;
			} else {
				result = false;
			}
		}
		return result;
	}

	@Override
	public List<Serializable> hibernateBatchSave(List<T> entitys) throws Exception {
		List<Serializable> ids = new ArrayList<Serializable>();
		Session session = getSession();
		for(int i = 0 ; i < entitys.size() ; i++) {
			Serializable id = this.save(entitys.get(i));
			ids.add(id);
			if(i % 20 == 0) {
				// 20个对象后才清理缓存，写入数据库
				session.flush();
				session.clear();
			}
		}
		// 最后清理一下----防止大于20小于40的不保存
		session.flush();
		session.clear();
		return ids;
	}

	@Override
	public boolean batchUpdate(List<T> entitys) throws Exception {
		return batchUpdate(entitys , true);
	}

	/**
	 * 批量修改实体
	 *
	 * @param entitys
	 *            需要修改的实体集合
	 */
	@Override
	public boolean batchUpdate(List<T> entitys , boolean isUpdateNullField) throws Exception {
		boolean result = false;
		if(entitys.size() > 0) {
			List<Object []> batchArgs = this.getBatchUpdateArgs(entitys , isUpdateNullField);
			if(isUpdateNullField) {
				T t = entitys.get(0);
				String sql = this.getBatchUpdateSql(t , isUpdateNullField);
				result = batchUpdate(sql , batchArgs);
			} else {
				int size = entitys.size();
				String sqls [] = new String [size];
				for(int i = 0 ; i < size ; i++) {
					T t = entitys.get(i);
					String sql = this.getBatchUpdateSql(t , isUpdateNullField);
					StringBuffer realSql = new StringBuffer();
					String [] sqlChunks = StringUtils.split(sql , "?");
					Object [] batchArg = batchArgs.get(i);
					for(int j = 0 , len = batchArg.length ; j < len ; j++) {
						realSql.append(sqlChunks[j]).append("'").append(StringEscapeUtils.escapeSql(batchArg[j].toString())).append("'");
					}
					sqls[i] = realSql.toString();
				}
				result = batchUpdate(sqls);
			}
		}
		return result;
	}

	@Override
	public boolean batchUpdate(String sql , List<Object []> batchArgs) {
		boolean result = false;
		if(batchArgs != null && batchArgs.size() > 0) {
			int [] batchSaveResult = this.jdbcTemplate.batchUpdate(sql , batchArgs);
			if(batchSaveResult != null && batchSaveResult.length > 0) {
				Arrays.sort(batchSaveResult);
				result = batchSaveResult[0] > 0;
			} else {
				result = false;
			}
		}
		return result;
	}

	@Override
	public boolean batchUpdate(String ... sqls) {
		boolean result = false;
		if(sqls != null && sqls.length > 0) {
			int [] batchSaveResult = this.jdbcTemplate.batchUpdate(sqls);
			if(batchSaveResult != null && batchSaveResult.length > 0) {
				Arrays.sort(batchSaveResult);
				result = batchSaveResult[0] > 0;
			} else {
				result = false;
			}
		}
		return result;
	}

	/**
	 * 批量删除记录
	 *
	 * @param cls
	 *            要删除的类
	 * @param ids
	 *            需要删除的记录主键集合
	 */

	@Override
	public boolean batchDelete(Class<T> cls , Integer ids []) throws Exception {
		return batchDelete(cls , StringUtils.join(ids , ","));
	}

	/**
	 * 批量删除记录
	 *
	 * @param cls
	 *            要删除的类
	 * @param ids
	 *            需要删除的记录主键集合
	 */

	@Override
	public boolean batchDelete(Class<T> cls , String ids []) throws Exception {
		String idsStr = "'" + StringUtils.join(ids , "','") + "'";
		return this.batchDelete(cls , idsStr);
	}

	/**
	 * 持久化一个对象
	 *
	 * @param entity
	 *            需要持久化的对象
	 */

	@Override
	public void saveOrUpdate(T entity) {
		try {
			hibernateTemplate.saveOrUpdate(entity);
			if(logger.isDebugEnabled())
				logger.debug("添加或更新成功," + entity.getClass().getName());
		} catch (RuntimeException e) {
			logger.error("添加或更新异常" , e);
			throw e;
		}
	}

	/**
	 * 删除一个对象
	 *
	 * @param entity
	 *            需要删除的对象
	 */

	@Override
	public void delete(T entity) {
		try {
			hibernateTemplate.delete(entity);
			hibernateTemplate.flush();
			if(logger.isDebugEnabled())
				logger.debug("删除成功," + entity.getClass().getName());
		} catch (RuntimeException e) {
			logger.error("删除异常" , e);
			throw e;
		}
	}

	/**
	 * 删除一个实体类指定Id的对象
	 *
	 * @param cls
	 *            实体
	 * @param id
	 *            主键Id
	 */

	@Override
	public void deleteEntityById(Class<T> cls , Serializable id) {
		T entity = get(cls , id);
		if(entity != null) {
			delete(entity);
		}
	}

	/**
	 * 批量删除对象
	 *
	 * @param entitys
	 *            需要删除的对象集合
	 */

	@Override
	public void batchDelete(Collection<T> entitys) {
		this.hibernateTemplate.deleteAll(entitys);
	}

	/**
	 * 修改一个实体类
	 *
	 * @param entity
	 *            需要修改的实体类对象
	 */

	@Override
	public void updateEntitiy(T entity) {
		hibernateTemplate.update(entity);
	}

	/**
	 * 通过sql语句更新数据
	 *
	 * @param sql
	 *            更新类型sql语句
	 *            （新增、删除、删除）
	 */
	@Override
	public int updateBySql(String sql) {
		Query querys = getSession().createSQLQuery(sql);
		return querys.executeUpdate();
	}

	/**
	 * 加载某一个实体的所有数据
	 *
	 * @param cls
	 *            实体类
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> loadAll(Class<T> cls) {
		Criteria criteria = createCriteria(cls);
		if(criteria != null) {
			return criteria.list();
		}
		return null;
	}

	/**
	 * 使用占位符的方式填充值 请注意：like对应的值格式："%"+username+"%" Hibernate Query
	 *
	 * @param hql
	 *            hql语句
	 * @param values
	 *            占位符号?对应的值，顺序必须一一对应 可以为空对象数组，但是不能为null
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> executeQuery(String hql , Object [] values) {
		Query query = getSession().createQuery(hql);
		for(int i = 0 ; (values != null) && (i < values.length) ; ++i) {
			query.setParameter(i , values[i]);
		}
		return query.list();
	}

	/**
	 * 执行存储过程(无结集)
	 *
	 * @param proc
	 *            存储过程名称
	 * @param params
	 *            存储过程参数,如：<br>
	 *            new SqlInOutParameter("inOutName" , Types.VARCHAR)
	 *            表示参数“inOutName”为输入输出参数<br>
	 *            new SqlOutParameter("outId" , Types.INTEGER)
	 *            表示参数“outId”为输出参数<br>
	 * @return
	 */
	@Override
	public Map<String , Object> callableStatementByName(String proc , List<SqlParameter> params) {
		final StringBuffer callProcedureSql = new StringBuffer("{call " + proc + "(");
		List<String> paramPlaceholder = new ArrayList<String>();
		for(int i = 0 ; i < params.size() ; i++) {
			paramPlaceholder.add("?");
		}
		callProcedureSql.append(StringUtils.join(paramPlaceholder , ",") + ")}");
		Map<String , Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {

			@Override
			public CallableStatement createCallableStatement(Connection conn) throws SQLException {
				return conn.prepareCall(callProcedureSql.toString());
			}
		} , params);
		return outValues;
	}

	/**
	 * 执行存储过程(有结果集)
	 *
	 * @param proc
	 *            存储过程名称
	 * @param params
	 *            存储过程参数,如：<br>
	 *            new SqlInOutParameter("inOutName" , Types.VARCHAR)
	 *            表示参数“inOutName”为输入输出参数<br>
	 *            new SqlOutParameter("outId" , Types.INTEGER)
	 *            表示参数“outId”为输出参数<br>
	 * @param outParam
	 *            除结果集外的输出参数容器，如果输出参数不需要接收可传入null
	 * @return
	 */
	@Override
	public List<Map<String , Object>> callableStatementByName(String proc , List<SqlParameter> params , Map<String , Object> outParam) {
		callableStatementResultList = new ArrayList<Map<String , Object>>();
		final StringBuffer callProcedureSql = new StringBuffer("{call " + proc + "(");
		List<String> paramPlaceholder = new ArrayList<String>();
		for(int i = 0 ; i < params.size() ; i++) {
			paramPlaceholder.add("?");
		}
		callProcedureSql.append(StringUtils.join(paramPlaceholder , ",") + ")}");
		Map<String , Object> outValues = jdbcTemplate.call(new CallableStatementCreator() {

			@Override
			public CallableStatement createCallableStatement(Connection conn) throws SQLException {
				CallableStatement prepareCall = conn.prepareCall(callProcedureSql.toString());
				ResultSet rs = null;
				try {
					// 封装结果集
					rs = prepareCall.getResultSet();
					ResultSetMetaData rsmd = rs.getMetaData();// 获取元数据
					while (rs.next()) {
						Map<String , Object> map = new HashMap<String , Object>();
						for(int j = 1 ; j <= rsmd.getColumnCount() ; j++) {
							map.put(rsmd.getColumnLabel(j) , rs.getString(j));
						}
						callableStatementResultList.add(map);
					}
				} catch (Exception e) {
					e.printStackTrace();
				} finally {
					rs.close();
				}
				return prepareCall;
			}
		} , params);
		if(outParam != null) {
			outParam = outValues;
		}
		return callableStatementResultList;
	}

	/**
	 * 根据Id获取一个实体对象
	 *
	 * @param cls
	 *            实体类
	 * @param id
	 *            主键id
	 */
	@Override
	public T get(Class<T> cls , Serializable id) {
		return (T)hibernateTemplate.get(cls , id);
	}

	/**
	 * 通过hql 查询语句查找对象,并以键值对返回
	 *
	 * @param hql
	 *            hql语句
	 * @return
	 */
	@SuppressWarnings("unchecked")
	@Override
	public Map<Object , Object> getHashMapbyHql(String hql) {
		Query query = getSession().createQuery(hql);
		List<Object []> list = query.list();
		Map<Object , Object> map = new HashMap<Object , Object>();
		for(Iterator<Object []> iterator = list.iterator() ; iterator.hasNext() ;) {
			Object [] tm = (Object [])iterator.next();
			map.put(tm[0].toString() , tm[1].toString());
		}
		return map;
	}

	/**
	 * 获取实体对应表或试图中的记录总数
	 *
	 * @param cls
	 *            实体类
	 * @return
	 */

	@Override
	public int getTableDataCount(Class<T> cls) {
		int count = DataAccessUtils.intResult(getSession().createQuery("select count(1) from " + cls.getName()).list());
		return count;
	}

	/**
	 * 执行更新操作，并返回受影响行数
	 *
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数
	 */
	@Override
	public Integer executeSql(String sql , List<Object> params) {
		return Integer.valueOf(this.jdbcTemplate.update(sql , new Object []{params}));
	}

	/**
	 * 执行更新操作，并返回受影响行数
	 *
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数
	 */
	@Override
	public Integer executeSql(String sql , Object [] params) {
		return Integer.valueOf(this.jdbcTemplate.update(sql , params));
	}

	/**
	 * 执行更新操作，并返回受影响行数
	 *
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数
	 */
	@Override
	public Integer executeSql(String sql , Map<String , Object> params) {
		return Integer.valueOf(this.namedParameterJdbcTemplate.update(sql , params));
	}

	/**
	 * 执行更新操作，并返回受影响的行数
	 *
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数
	 */
	@Override
	public Object executeSqlReturnKey(String sql , Map<String , Object> params) {
		Object keyValue = null;
		KeyHolder keyHolder = new GeneratedKeyHolder();
		SqlParameterSource sqlp = new MapSqlParameterSource(params);
		this.namedParameterJdbcTemplate.update(sql , sqlp , keyHolder);
		keyValue = Long.valueOf(keyHolder.getKey().longValue());
		return keyValue;
	}

	/**
	 * 执行hql更新数据，并返回受影响行数
	 *
	 * @param hql
	 *            hql语句
	 */
	@Override
	public Integer executeHql(String hql) {
		Query q = getSession().createQuery(hql);
		return Integer.valueOf(q.executeUpdate());
	}

	/**
	 * 执行hql语句查询查找对象
	 *
	 * @param hql
	 *            hql语句
	 * @param params
	 *            参数
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> findByHql(String hql , Object [] params) {
		Query q = getSession().createQuery(hql);
		if((params != null) && (params.length > 0)) {
			for(int i = 0 ; i < params.length ; ++i) {
				q.setParameter(i , params[i]);
			}
		}
		return q.list();
	}

	/**
	 * 根据属性值该实体的唯一记录，如果有多条记录，将返回第一条记录
	 *
	 * @param entityClass
	 *            实体类
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @return
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public T findUniqueByProperty(Class<T> entityClass , String propertyName , Object value) {
		Assert.hasText(propertyName);
		if(value != null) {
			return (T)createCriteria(entityClass , new Criterion []{Restrictions.eq(propertyName , value)}).uniqueResult();
		} else {
			return (T)createCriteria(entityClass , new Criterion []{Restrictions.isNull(propertyName)}).uniqueResult();
		}
	}

	/**
	 * 根据属性值该实体类的记录
	 *
	 * @param entityClass
	 *            实体类
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @return
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> findByProperty(Class<T> entityClass , String propertyName , Object value) {
		Assert.hasText(propertyName);
		if(value != null) {
			return createCriteria(entityClass , new Criterion []{Restrictions.eq(propertyName , value)}).list();
		} else {
			return createCriteria(entityClass , new Criterion []{Restrictions.isNull(propertyName)}).list();
		}
	}

	/**
	 * 通过hql 查询语句查找对象，并以实体类型集合返回
	 *
	 * @param hql
	 *            hql语句
	 * @return
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> findByHql(String hql) {
		Query queryObject = getSession().createQuery(hql);
		List<T> list = queryObject.list();
		if(list.size() > 0) {
			getSession().flush();
		}
		return list;
	}

	/**
	 * 通过sql查询语句查找对象
	 *
	 * @param sql
	 *            查询类型sql语句，sql语句中的查询库字段别名，必须是cls参数中存在的属性名
	 * @param cls
	 *            封装查询数据的对象（此参数可以不受Hibernate管制，普通的pojo即可）
	 * @param params
	 *            SQL语句中的参数，请注意参数与sql语句中参数占位符?的对应关系
	 * @return
	 * @throws Exception
	 * @throws IllegalAccessException
	 * @throws InstantiationException
	 */
	@SuppressWarnings("unchecked")
	@Override
	public List<T> findListBySql(String sql , Class<T> cls , Object ... params) throws Exception {
		List<T> resultList = new ArrayList<T>();
		Field fields [] = cls.getDeclaredFields();
		List<Map<String , Object>> list = this.findForJdbc(sql , params);
		try {
			for(Object object : list) {
				T t = cls.newInstance();
				if(object instanceof Map) {
					Map<String , Object> map = (Map<String , Object>)object;
					Set<String> columnKeySet = map.keySet();
					for(Field f : fields) {
						String fieldName = f.getName();
						if(columnKeySet.contains(fieldName)) {
							f.setAccessible(true);
							Object fieldValue = f.get(t);
							Object values_obj = map.get(fieldName);
							if(values_obj != null && !values_obj.equals(fieldValue)) {
								Class<?> fieldType = f.getType();
								String fieldTypeName = fieldType.getSimpleName();
								String value_str = values_obj + "";
								/**
								 * 如果是基本数据类型时（如int、float、double、byte、char、
								 * boolean）
								 * 需要先将Object转换成相应的封装类之后再转换成对应的基本数据类型
								 * 否则会报 ClassCastException
								 **/
								if(fieldTypeName.equals("BigDecimal")) {
									f.set(t , new BigDecimal(value_str));
								} else if(fieldTypeName.toLowerCase().equals("float")) {
									f.set(t , Float.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("byte")) {
									f.set(t , Byte.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("short")) {
									f.set(t , Short.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("double")) {
									f.set(t , Double.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("boolean")) {
									f.set(t , Boolean.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("long")) {
									f.set(t , Long.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("int") || fieldTypeName.equals("Integer")) {
									f.set(t , Integer.valueOf(value_str));
								} else if(fieldTypeName.toLowerCase().equals("char") || fieldTypeName.equals("Character")) {
									f.set(t , Character.valueOf(value_str.charAt(0)));
								} else if(fieldTypeName.equals("Date") || fieldTypeName.equals("Timestamp")) {
									f.set(t , Timestamp.valueOf(value_str));
								} else {
									f.set(t , String.valueOf(value_str));
								}
							}
						}
					}
				}
				resultList.add(t);
			}
		} catch (Exception e) {
			resultList.clear();
			throw e;
		}
		return resultList;
	}

	/**
	 * 将原生sql返回的结果集注入到指定的可不受Hibernate管制实体bean中（待测试）
	 *
	 * @param sql
	 *            查询语句
	 * @param columnAliases
	 *            手动指定需要返回的数据库字段别名，别名必须是cls参数中存在的属性名
	 * @param cls
	 *            封装查询数据的对象（此参数可以不受Hibernate管制，普通的pojo即可）
	 * @param params
	 *            SQL语句中的参数，请注意参数与sql语句中参数占位符?的对应关系
	 * @return
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> findListBySql(String sql , String [] columnAliases , Class<T> cls , Object ... params) {
		SQLQuery sqlQuery = getSession().createSQLQuery(sql);
		if(params != null && params.length > 0) {
			for(int i = 0 ; i < params.length ; i++) {
				sqlQuery.setParameter(i , params[i]);// 设置参数
			}
		}
		for(String columnAlias : columnAliases) {
			sqlQuery.addScalar(columnAlias);// 添加需要返回的字段别名
		}
		ResultTransformer aliasToBean = Transformers.aliasToBean(cls);
		sqlQuery.setResultTransformer(aliasToBean);
		return sqlQuery.list();
	}

	/**
	 * 根据属性值该实体类的记录
	 *
	 * @param cls
	 *            实体类
	 * @param propertyName
	 *            属性名
	 * @param value
	 *            属性值
	 * @param isAsc
	 *            是否升序排列
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> findByPropertyisOrder(Class<T> cls , String propertyName , Object value , boolean isAsc) {
		Assert.hasText(propertyName);
		return createCriteria(cls , isAsc , new Criterion []{Restrictions.eq(propertyName , value)}).list();
	}

	/**
	 * 执行查询操作，并将结果集映射到map中<br>
	 * <span style="color:red;">返回的结果集大小必须是1</span>
	 *
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数
	 */
	@Override
	public Map<String , Object> findOneForJdbc(String sql , Object [] params) {
		try {
			return this.jdbcTemplate.queryForMap(sql , params);
		} catch (EmptyResultDataAccessException e) {
		}
		return null;
	}

	/**
	 * 执行sql查询，并返回结果集
	 *
	 * @param sql
	 *            sql语句
	 * @param params
	 *            参数
	 */
	@Override
	public List<Map<String , Object>> findForJdbc(String sql , Object [] params) {
		return this.jdbcTemplate.queryForList(sql , params);
	}

	/**
	 * 通过DetachedCriteria查询数据集
	 *
	 * @param dc
	 *            查询条件对象
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public List<T> findByDetached(DetachedCriteria dc) {
		Session session = getSession();
		return dc.getExecutableCriteria(session).list();
	}

	/**
	 * 通过DetachedCriteria查询指定下标开始查询指定数据量的的数据集
	 *
	 * @param dc
	 *            查询条件对象
	 * @param pageNo
	 *            当前页
	 * @param pageSize
	 *            的最大数量的结果
	 */
	@SuppressWarnings({"unchecked"})
	@Override
	public PageBean pageList(DetachedCriteria dc , int pageNo , int pageSize) {
		Criteria criteria = dc.getExecutableCriteria(getSession());
		// 获取总记录数
		Object count = criteria.setProjection(Projections.rowCount()).uniqueResult();
		int sumCount = count == null ? 0 : Integer.parseInt(count.toString());
		// 设置投影为空,并进行分页查询
		criteria.setProjection(null);
		criteria.setResultTransformer(CriteriaSpecification.ROOT_ENTITY);
		criteria.setFirstResult((pageNo - 1) * pageSize);
		criteria.setMaxResults(pageSize);
		List<T> list = criteria.list();
		sumCount = sumCount <= 0 ? 1 : sumCount;
		return new PageBean(pageNo , pageSize , sumCount , list);
	}

	/**
	 * 使用指定的sql语句检索数据并分页返回数据
	 *
	 * @param sql
	 *            要执行查询的sql，此sql只需要专注查询，不需要写分页相关的代码
	 * @param pageNo
	 *            当前页
	 * @param pageSize
	 *            每一页显示的记录数
	 * @return
	 */
	@Override
	public PageBean pageList(String sql , int pageNo , int pageSize) {
		// 创建分页SQL
		String sql_results = "select count(1) count from (" + sql + ") tempTable";
		Map<String , Object> map = this.findOneForJdbc(sql_results , null);
		int sumCount = map == null ? 0 : Integer.parseInt(map.get("count") == null ? "0" : map.get("count").toString());
		sumCount = sumCount <= 0 ? 1 : sumCount;
		sql = createPageSql(sql , pageNo , pageSize);
		List<Map<String , Object>> list = this.jdbcTemplate.queryForList(sql);
		return new PageBean(pageNo , pageSize , sumCount , list);
	}

	/**
	 * 按照数据库类型，将sql格式化为分页SQL
	 *
	 * @param sql
	 *            需要转换成分页的sql语句
	 * @param pageNo
	 *            当前页
	 * @param pageSize
	 *            每一页显示的记录数
	 * @return
	 */
	private static String createPageSql(String sql , int pageNo , int pageSize) {
		int beginNum = (pageNo - 1) * pageSize;
		Object [] sqlParam = new String [3];
		sqlParam[0] = sql;
		sqlParam[1] = beginNum + "";
		sqlParam[2] = pageSize + "";
		if(DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_MYSQL) != -1) {
			sql = MessageFormat.format(MYSQL_SQL , sqlParam);
		} else if(DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_POSTGRE) != -1) {
			sql = MessageFormat.format(POSTGRE_SQL , sqlParam);
		} else {
			int beginIndex = (pageNo - 1) * pageSize;
			int endIndex = beginIndex + pageSize;
			sqlParam[2] = Integer.toString(beginIndex);
			sqlParam[1] = Integer.toString(endIndex);
			if(DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_ORACLE) != -1) {
				sql = MessageFormat.format(ORACLE_SQL , sqlParam);
			} else if(DBTypeUtil.getDBType().toLowerCase().indexOf(DATABSE_TYPE_SQLSERVER) != -1) {
				sqlParam[0] = sql.substring(getAfterSelectInsertPoint(sql));
				sql = MessageFormat.format(SQLSERVER_SQL , sqlParam);
			}
		}
		return sql;
	}

	private static int getAfterSelectInsertPoint(String sql) {
		int selectIndex = sql.toLowerCase().indexOf("select");
		int selectDistinctIndex = sql.toLowerCase().indexOf("select distinct");
		return selectIndex + (selectDistinctIndex == selectIndex ? 15 : 6);
	}

	/**
	 * 批量删除记录
	 *
	 * @param cls
	 *            要删除的实体
	 * @param ids
	 *            要删除记录的Id，格式如：1,3,4,5....
	 * @return
	 * @throws Exception
	 */

	private boolean batchDelete(Class<T> cls , String ids) throws Exception {
		boolean result = false;
		if(StringUtils.isNotEmpty(ids)) {
			Field [] fields = cls.getDeclaredFields();
			Method method = null;
			Id id = null;
			String idColumnName = null;
			for(Field field : fields) {
				method = getMethod(cls , field);
				id = null;// 主键
				id = getId(method , field);
				if(id != null) {
					Column column = this.getColumn(method , field);
					if(column != null) {
						idColumnName = field.getName();
					}
					break;
				}
			}
			try {
				if(idColumnName == null) {
					throw new Exception(cls.getName() + " 未设置主键!");
				}
				DetachedCriteria dc = DetachedCriteria.forClass(cls);
				Object [] idss = ids.split(",");
				Criterion in = Restrictions.in(idColumnName , idss);
				System.out.println(in);
				dc.add(in);
				List<?> entities = findByDetached(dc);
				if(entities != null && !entities.isEmpty()) {
					this.hibernateTemplate.deleteAll(entities);
				}
				result = true;
			} catch (Exception e) {
				throw e;
			}
		}
		return result;
	}

	private Criteria createCriteria(Class<T> entityClass , boolean isAsc , Criterion [] criterions) {
		Criteria criteria = createCriteria(entityClass , criterions);
		if(isAsc)
			criteria.addOrder(Order.asc("asc"));
		else {
			criteria.addOrder(Order.desc("desc"));
		}
		return criteria;
	}

	private Criteria createCriteria(Class<T> entityClass , Criterion [] criterions) {
		Criteria criteria = getSession().createCriteria(entityClass);
		for(Criterion c : criterions) {
			criteria.add(c);
		}
		return criteria;
	}

	private Criteria createCriteria(Class<T> entityClass) {
		if(getSession() != null) {
			Criteria criteria = getSession().createCriteria(entityClass);
			return criteria;
		}
		return null;
	}

	/**
	 * 获取某一实体类的插入sql语句
	 *
	 * @param t
	 *            实体对象
	 * @return
	 * @throws Exception
	 */

	private String getBatchInsertSql(T t) throws Exception {
		Class<?> clazz = t.getClass();
		StringBuilder sql = new StringBuilder();
		StringBuilder insertSql = new StringBuilder();
		StringBuilder valuessql = new StringBuilder();
		if(!clazz.isAnnotationPresent(Table.class)) {
			sql.append("INSERT INTO " + clazz.getName());
		} else {
			Table table = (Table)clazz.getAnnotation(Table.class);
			if("".equals(table.name())) {
				sql.append("INSERT INTO " + clazz.getName());
			} else {
				sql.append("INSERT INTO " + table.name());
			}
		}
		Field [] fields = clazz.getDeclaredFields();
		Method method;
		insertSql.append(" (");
		valuessql.append(" values ( ");
		for(Field field : fields) {
			method = getMethod(clazz , field);
			if(method != null) {
				Transactional transactionalNmae = null;// 是否为临时属性
				try {
					transactionalNmae = method.getAnnotation(Transactional.class);
				} catch (Exception e) {
					transactionalNmae = field.getAnnotation(Transactional.class);
				}
				if(transactionalNmae != null) {
					continue;
				}
				Column columnName = null;
				try {
					columnName = (Column)method.getAnnotation(Column.class);
					if(columnName == null) {
						columnName = field.getAnnotation(Column.class);
					}
				} catch (Exception e) {
					columnName = field.getAnnotation(Column.class);
				}
				if(columnName != null) {
					field.setAccessible(true);
					Object value = field.get(t);
					if(value != null) {
						if(method.isAnnotationPresent(Table.class)) {
							if("".equals(columnName.name())) {
								insertSql.append(field.getName() + ",");
							} else {
								insertSql.append(columnName.name() + ",");
							}
						} else {
							insertSql.append(field.getName() + ",");
						}
						valuessql.append("?,");
					}
				}
			}
		}
		sql.append(insertSql.substring(0 , insertSql.length() - 1)).append(" ) ").append(valuessql.substring(0 , valuessql.length() - 1)).append(" )");
		logger.info(sql);
		return sql.toString();
	}

	/**
	 * 获取某一实体类的修改的sql语句
	 *
	 * @param t
	 *            实体对象
	 * @return
	 * @throws Exception
	 */

	private String getBatchUpdateSql(T t , boolean isUpdateNullField) throws Exception {
		Class<?> clazz = t.getClass();
		StringBuilder sql = new StringBuilder();
		StringBuilder set = new StringBuilder();
		StringBuilder wheresql = new StringBuilder();
		Method method = null;
		if(!clazz.isAnnotationPresent(Table.class)) {
			sql.append("UPDATE " + clazz.getName());
		} else {
			Table table = (Table)clazz.getAnnotation(Table.class);
			if("".equals(table.name())) {
				sql.append("UPDATE " + clazz.getName());
			} else {
				sql.append("UPDATE " + table.name());
			}
		}
		Field [] fields = clazz.getDeclaredFields();
		set.append(" set ");
		wheresql.append(" where 1=1 ");
		for(Field field : fields) {
			method = getMethod(clazz , field);
			if(method != null) {
				field.setAccessible(true);
				if(method.isAnnotationPresent(Column.class)) {
					Transactional transactional = null;// 是否为临时属性
					transactional = getTransactional(method , field);
					if(transactional != null) {
						continue;
					}
					Column columnName = getColumn(method , field);
					Id id = getId(method , field);// 主键
					if(id == null) {
						Object value = field.get(t);
						if(value != null || isUpdateNullField) {
							if("".equals(columnName.name())) {
								set.append(" " + field.getName() + " = ?,");
							} else {
								set.append(" " + columnName.name() + " = ?,");
							}
						} else {
							if("".equals(columnName.name())) {
								set.append(" " + field.getName() + " = " + field.getName() + ",");
							} else {
								set.append(" " + columnName.name() + " = " + columnName.name() + ",");
							}
						}
					} else {
						wheresql.append(" and " + columnName.name() + " = ?,");
					}
				}
			}
		}
		if(" where 1=1 ".equals(wheresql)) {
			throw new RuntimeException("实体变量没有设置ID字段值");
		}
		sql.append(set.substring(0 , set.length() - 1)).append(wheresql.subSequence(0 , wheresql.length() - 1));
		logger.info(sql);
		return sql.toString();
	}

	/**
	 * 获取某一属性的Id注解
	 *
	 * @param method
	 *            该属性的getter方法
	 * @param field
	 *            属性
	 * @return
	 */
	protected Id getId(Method method , Field field) {
		Id id;
		try {
			id = method.getAnnotation(Id.class);
			if(id == null) {
				id = field.getAnnotation(Id.class);
			}
		} catch (Exception e) {
			id = field.getAnnotation(Id.class);
		}
		return id;
	}

	/**
	 * @param method
	 * @param field
	 * @return
	 */
	private Transactional getTransactional(Method method , Field field) {
		Transactional transactional;
		try {
			transactional = method.getAnnotation(Transactional.class);
			if(transactional == null) {
				transactional = field.getAnnotation(Transactional.class);
			}
		} catch (Exception e) {
			transactional = field.getAnnotation(Transactional.class);
		}
		return transactional;
	}

	/**
	 * getBatchSaveArgs
	 * 获取批量新建对象集合中与数据库对应的字段的值
	 *
	 * @param entitys
	 *            实体对象集合
	 * @return
	 * @throws Exception
	 */
	private List<Object []> getBatchInsertArgs(List<T> entitys) throws Exception {
		List<Object []> batchArgs = new ArrayList<Object []>();
		Class<?> clazz = null;
		if(entitys.size() > 0) {
			clazz = entitys.get(0).getClass();
		} else {
			return batchArgs;
		}
		if(clazz.isAnnotationPresent(Table.class)) {
			Field [] fields = clazz.getDeclaredFields();
			Method method;
			for(Object t : entitys) {
				List<Object> values = new ArrayList<Object>();
				for(Field field : fields) {
					method = getMethod(clazz , field);
					if(method != null) {
						Column columnName = getColumn(method , field);
						if(columnName != null) {
							field.setAccessible(true);
							Object value = field.get(t);
							if(value != null) {
								values.add(value);
							}
						}
					}
				}
				batchArgs.add(values.toArray());
			}
		}
		return batchArgs;
	}

	/**
	 * 获取批量更新对象集合中与数据库对应的字段的值
	 *
	 * @param entitys
	 *            实体对象集合
	 * @return
	 * @throws Exception
	 */
	private List<Object []> getBatchUpdateArgs(List<T> entitys , boolean isUpdateNullField) throws Exception {
		List<Object []> batchArgs = new ArrayList<Object []>();
		Class<?> clazz = null;
		if(entitys.size() > 0) {
			clazz = entitys.get(0).getClass();
		} else {
			return batchArgs;
		}
		if(clazz.isAnnotationPresent(Table.class)) {
			Field [] fields = clazz.getDeclaredFields();
			Method method;
			for(Object t : entitys) {
				Object idValue = null;// 主键值
				List<Object> values = new ArrayList<Object>();
				for(Field field : fields) {
					method = getMethod(clazz , field);
					if(method != null) {
						Id id = getId(method , field);
						Column columnName = getColumn(method , field);
						if(columnName != null) {
							field.setAccessible(true);
							Object value = field.get(t);
							if(value != null || isUpdateNullField) {
								if(id != null) {
									idValue = value;
								} else {
									values.add(value);
								}
							}
						}
					}
				}
				if(idValue == null) {
					throw new Exception("无法更新,包含没有主键的记录!");
				}
				values.add(idValue);// 主键值放在最后作为修改的必要条件
				batchArgs.add(values.toArray());
			}
		}
		return batchArgs;
	}

	/**
	 * @param method
	 * @param field
	 * @return
	 */
	private Column getColumn(Method method , Field field) {
		Column columnName;
		try {
			columnName = method.getAnnotation(Column.class);
			if(columnName == null) {
				columnName = field.getAnnotation(Column.class);
			}
		} catch (Exception e) {
			columnName = field.getAnnotation(Column.class);
		}
		return columnName;
	}

	/**
	 * 获取属性对应的getter方法
	 *
	 * @param clazz
	 *            类型
	 * @param field
	 *            属性
	 * @return
	 * @throws NoSuchMethodException
	 */
	protected Method getMethod(Class<?> clazz , Field field) throws NoSuchMethodException {
		Method method = null;
		String fieldName = field.getName();// 实体类映射名
		String methodSuffix = com.rybbaby.tss.tasks.core.utils.StringUtils .getGetterOrSetterSuffix(fieldName);
		String getterMentodName = "get" + methodSuffix;
		String getterMentodName_od = null;
		if(fieldName.substring(0 , 2).toLowerCase().equals("is")) {
			getterMentodName_od = getterMentodName;
			getterMentodName = "is" + methodSuffix;
		}
		try {
			method = clazz.getMethod(getterMentodName);
		} catch (Exception e) {
			if(getterMentodName_od != null) {
				method = clazz.getMethod(getterMentodName_od);
			}
		}
		return method;
	}
}
